********************************************************************************
** 	TITLE:		DE09_13_results                                               ** 	
**	AUTHOR:	    Philippe Mongrain                                             **
**	DATA:       btw13_kerg                                                    **
**	VERSION:	Stata 16					                                  **
**	DATE:		October 2022  				                                  **
********************************************************************************

* Version control

version 16.0

* Note: Running this do-file generates the 2009 results according to the 2013 district boudaries

* 1. Download the "btw13_kerg.csv" file from this link: https://www.bundeswahlleiter.de/en/dam/jcr/2ace94c3-15cb-476b-aa98-806b97209353/btw13_kerg.csv
* 2. Save the "btw13_kerg.csv" file to the Excel format (i.e., "btw13_kerg.xlsx"")
* 3. Verify that the name of the Excel sheet is "btw13_kerg"

* Import the dataset

import excel "btw13_kerg.xlsx", sheet("btw13_kerg") cellrange(A9:FC340) clear

* Rename variables

rename A districtnumber13
rename B districtname13
rename C state13
rename D v13_1_Wahlberechtigte_1_End
rename E v09_1_Wahlberechtigte_1_Vor
rename F v13_2_Wahlberechtigte_2_End
rename G v09_2_Wahlberechtigte_2_Vor
rename H v13_1_Wähler_1_End
rename I v09_1_Wähler_1_Vor
rename J v13_2_Wähler_2_End
rename K v09_2_Wähler_2_Vor
rename L v13_1_Ungültige_1_End
rename M v09_1_Ungültige_1_Vor
rename N v13_2_Ungültige_2_End
rename O v09_2_Ungültige_2_Vor
rename P v13_1_Gültige_1_End
rename Q v09_1_Gültige_1_Vor
rename R v13_2_Gültige_2_End
rename S v09_2_Gültige_2_Vor
rename T v13_1_CDU_1_End
rename U v09_1_CDU_1_Vor
rename V v13_2_CDU_2_End
rename W v09_2_CDU_2_Vor
rename X v13_1_SPD_1_End
rename Y v09_1_SPD_1_Vor
rename Z v13_2_SPD_2_End
rename AA v09_2_SPD_2_Vor
rename AB v13_1_FDP_1_End
rename AC v09_1_FDP_1_Vor
rename AD v13_2_FDP_2_End
rename AE v09_2_FDP_2_Vor
rename AF v13_1_DIELINKE_1_End
rename AG v09_1_DIELINKE_1_Vor
rename AH v13_2_DIELINKE_2_End
rename AI v09_2_DIELINKE_2_Vor
rename AJ v13_1_GRÜNE_1_End
rename AK v09_1_GRÜNE_1_Vor
rename AL v13_2_GRÜNE_2_End
rename AM v09_2_GRÜNE_2_Vor
rename AN v13_1_CSU_1_End
rename AO v09_1_CSU_1_Vor
rename AP v13_2_CSU_2_End
rename AQ v09_2_CSU_2_Vor
rename AR v13_1_PIRATEN_1_End
rename AS v09_1_PIRATEN_1_Vor
rename AT v13_2_PIRATEN_2_End
rename AU v09_2_PIRATEN_2_Vor
rename AV v13_1_NPD_1_End
rename AW v09_1_NPD_1_Vor
rename AX v13_2_NPD_2_End
rename AY v09_2_NPD_2_Vor
rename AZ v13_1_Tierschutzpartei_1_End
rename BA v09_1_Tierschutzpartei_1_Vor
rename BB v13_2_Tierschutzpartei_2_End
rename BC v09_2_Tierschutzpartei_2_Vor
rename BD v13_1_REP_1_End
rename BE v09_1_REP_1_Vor
rename BF v13_2_REP_2_End
rename BG v09_2_REP_2_Vor
rename BH v13_1_ÖDP_1_End
rename BI v09_1_ÖDP_1_Vor
rename BJ v13_2_ÖDP_2_End
rename BK v09_2_ÖDP_2_Vor
rename BL v13_1_FAMILIE_1_End
rename BM v09_1_FAMILIE_1_Vor
rename BN v13_2_FAMILIE_2_End
rename BO v09_2_FAMILIE_2_Vor
rename BP v13_1_Bündnis21RRP_1_End
rename BQ v09_1_Bündnis21RRP_1_Vor
rename BR v13_2_Bündnis21RRP_2_End
rename BS v09_2_Bündnis21RRP_2_Vor
rename BT v13_1_RENTNER_1_End
rename BU v09_1_RENTNER_1_Vor
rename BV v13_2_RENTNER_2_End
rename BW v09_2_RENTNER_2_Vor
rename BX v13_1_BP_1_End
rename BY v09_1_BP_1_Vor
rename BZ v13_2_BP_2_End
rename CA v09_2_BP_2_Vor
rename CB v13_1_PBC_1_End
rename CC v09_1_PBC_1_Vor
rename CD v13_2_PBC_2_End
rename CE v09_2_PBC_2_Vor
rename CF v13_1_BüSo_1_End
rename CG v09_1_BüSo_1_Vor
rename CH v13_2_BüSo_2_End
rename CI v09_2_BüSo_2_Vor
rename CJ v13_1_DIEVIOLETTEN_1_End
rename CK v09_1_DIEVIOLETTEN_1_Vor
rename CL v13_2_DIEVIOLETTEN_2_End
rename CM v09_2_DIEVIOLETTEN_2_Vor
rename CN v13_1_MLPD_1_End
rename CO v09_1_MLPD_1_Vor
rename CP v13_2_MLPD_2_End
rename CQ v09_2_MLPD_2_Vor
rename CR v13_1_Volksabstimmung_1_End
rename CS v09_1_Volksabstimmung_1_Vor
rename CT v13_2_Volksabstimmung_2_End
rename CU v09_2_Volksabstimmung_2_Vor
rename CV v13_1_PSG_1_End
rename CW v09_1_PSG_1_Vor
rename CX v13_2_PSG_2_End
rename CY v09_2_PSG_2_Vor
rename CZ v13_1_AfD_1_End
rename DA v09_1_AfD_1_Vor
rename DB v13_2_AfD_2_End
rename DC v09_2_AfD_2_Vor
rename DD v13_1_BIG_1_End
rename DE v09_1_BIG_1_Vor
rename DF v13_2_BIG_2_End
rename DG v09_2_BIG_2_Vor
rename DH v13_1_proDeutschland_1_End
rename DI v09_1_proDeutschland_1_Vor
rename DJ v13_2_proDeutschland_2_End
rename DK v09_2_proDeutschland_2_Vor
rename DL v13_1_DIERECHTE_1_End
rename DM v09_1_DIERECHTE_1_Vor
rename DN v13_2_DIERECHTE_2_End
rename DO v09_2_DIERECHTE_2_Vor
rename DP v13_1_DIEFRAUEN_1_End
rename DQ v09_1_DIEFRAUEN_1_Vor
rename DR v13_2_DIEFRAUEN_2_End
rename DS v09_2_DIEFRAUEN_2_Vor
rename DT v13_1_FREIEWÄHLER_1_End
rename DU v09_1_FREIEWÄHLER_1_Vor
rename DV v13_2_FREIEWÄHLER_2_End
rename DW v09_2_FREIEWÄHLER_2_Vor
rename DX v13_1_Nichtwähler_1_End
rename DY v09_1_Nichtwähler_1_Vor
rename DZ v13_2_Nichtwähler_2_End
rename EA v09_2_Nichtwähler_2_Vor
rename EB v13_1_PARTEIDERVERNUNFT_1_End
rename EC v09_1_PARTEIDERVERNUNFT_1_Vor
rename ED v13_2_PARTEIDERVERNUNFT_2_End
rename EE v09_2_PARTEIDERVERNUNFT_2_Vor
rename EF v13_1_DiePARTEI_1_End
rename EG v09_1_DiePARTEI_1_Vor
rename EH v13_2_DiePARTEI_2_End
rename EI v09_2_DiePARTEI_2_Vor
rename EJ v13_1_B_1_End
rename EK v09_1_B_1_Vor
rename EL v13_2_B_2_End
rename EM v09_2_B_2_Vor
rename EN v13_1_BGD_1_End
rename EO v09_1_BGD_1_Vor
rename EP v13_2_BGD_2_End
rename EQ v09_2_BGD_2_Vor
rename ER v13_1_DKP_1_End
rename ES v09_1_DKP_1_Vor
rename ET v13_2_DKP_2_End
rename EU v09_2_DKP_2_Vor
rename EV v13_1_NEIN_1_End
rename EW v09_1_NEIN_1_Vor
rename EX v13_2_NEIN_2_End
rename EY v09_2_NEIN_2_Vor
rename EZ v13_1_Übrige_1_End
rename FA v09_1_Übrige_1_Vor
rename FB v13_2_Übrige_2_End
rename FC v09_2_Übrige_2_Vor

* Reshape the dataset

drop if districtnumber13 == .
drop if districtname13 == "Bundesgebiet"
drop districtname13
drop if state13 == 99
drop state13

reshape long v13_1_ v09_1_ v13_2_ v09_2_, i(districtnumber13) j(var) string

* Drop second vote and 2013 results

drop if strpos(var, "_2_") > 0
drop if strpos(var, "_End") > 0
drop v09_2_ v13_1_ v13_2_

* Generate dichotomous variable for each party 

gen cdu09 = strpos(var, "CDU") > 0
gen spd09 = strpos(var, "SPD") > 0
gen fdp09 = strpos(var, "FDP") > 0
gen dielinke09 = strpos(var, "DIELINKE") > 0
gen grune09 = strpos(var, "GRÜNE") > 0
gen csu09 = strpos(var, "CSU") > 0
gen piraten09 = strpos(var, "PIRATEN") > 0
gen npd09 = strpos(var, "NPD") > 0
gen tier09 = strpos(var, "Tierschutzpartei") > 0
gen rep09 = strpos(var, "REP") > 0
gen odp09 = strpos(var, "ÖDP") > 0
gen familie09 = strpos(var, "FAMILIE") > 0
gen bundnis09 = strpos(var, "Bündnis21RRP") > 0
gen rentner09 = strpos(var, "RENTNER") > 0
gen bp09 = strpos(var, "BP") > 0
gen pbc09 = strpos(var, "PBC") > 0
gen buso09 = strpos(var, "BüSo") > 0
gen violetten09 = strpos(var, "DIEVIOLETTEN") > 0
gen mlpd09 = strpos(var, "MLPD") > 0
gen volks09 = strpos(var, "Volksabstimmung") > 0
gen psg09 = strpos(var, "PSG") > 0
gen afd09 = strpos(var, "AfD") > 0
gen big09 = strpos(var, "BIG") > 0
gen pro09 = strpos(var, "proDeutschland") > 0
gen dierechte09 = strpos(var, "DIERECHTE") > 0
gen diefrauen09 = strpos(var, "DIEFRAUEN") > 0
gen freiewahler09 = strpos(var, "FREIEWÄHLER") > 0
gen nichtwahler09 = strpos(var, "Nichtwähler") > 0
gen vernunft09 = strpos(var, "PARTEIDERVERNUNFT") > 0
gen diepartei09 = strpos(var, "DiePARTEI") > 0
gen b09 = strpos(var, "B") > 0
gen bgd09 = strpos(var, "BGD") > 0
gen dkp09 = strpos(var, "DKP") > 0
gen nein09 = strpos(var, "NEIN") > 0
gen other09 = strpos(var, "Übrige") > 0

* Generate party variable

gen party09 = 1 if cdu09 == 1
replace party09 = 4 if spd09== 1
replace party09 = 5 if fdp09 == 1
replace party09 = 7 if dielinke09 == 1
replace party09 = 6 if grune09 == 1
replace party09 = 1 if csu09 == 1
replace party09 = 88 if party09 == . 

* Generate district names

gen districtname13 = ""

replace districtname13 = "Flensburg - Schleswig" if districtnumber13 == 1
replace districtname13 = "Nordfriesland - Dithmarschen Nord" if districtnumber13 == 2
replace districtname13 = "Steinburg - Dithmarschen Süd" if districtnumber13 == 3
replace districtname13 = "Rendsburg-Eckernförde" if districtnumber13 == 4
replace districtname13 = "Kiel" if districtnumber13 == 5
replace districtname13 = "Plön - Neumünster" if districtnumber13 == 6
replace districtname13 = "Pinneberg" if districtnumber13 == 7
replace districtname13 = "Segeberg - Stormarn-Mitte" if districtnumber13 == 8
replace districtname13 = "Ostholstein - Stormarn-Nord" if districtnumber13 == 9
replace districtname13 = "Herzogtum Lauenburg - Stormarn-Süd" if districtnumber13 == 10
replace districtname13 = "Lübeck" if districtnumber13 == 11
replace districtname13 = "Schleswig-Holstein" if districtnumber13 == 901
replace districtname13 = "Hamburg-Mitte" if districtnumber13 == 18
replace districtname13 = "Hamburg-Altona" if districtnumber13 == 19
replace districtname13 = "Hamburg-Eimsbüttel" if districtnumber13 == 20
replace districtname13 = "Hamburg-Nord" if districtnumber13 == 21
replace districtname13 = "Hamburg-Wandsbek" if districtnumber13 == 22
replace districtname13 = "Hamburg-Bergedorf - Harburg" if districtnumber13 == 23
replace districtname13 = "Hamburg" if districtnumber13 == 902
replace districtname13 = "Aurich - Emden" if districtnumber13 == 24
replace districtname13 = "Unterems" if districtnumber13 == 25
replace districtname13 = "Friesland - Wilhelmshaven - Wittmund" if districtnumber13 == 26
replace districtname13 = "Oldenburg - Ammerland" if districtnumber13 == 27
replace districtname13 = "Delmenhorst - Wesermarsch - Oldenburg-Land" if districtnumber13 == 28
replace districtname13 = "Cuxhaven - Stade II" if districtnumber13 == 29
replace districtname13 = "Stade I - Rotenburg II" if districtnumber13 == 30
replace districtname13 = "Mittelems" if districtnumber13 == 31
replace districtname13 = "Cloppenburg - Vechta" if districtnumber13 == 32
replace districtname13 = "Diepholz - Nienburg I" if districtnumber13 == 33
replace districtname13 = "Osterholz - Verden" if districtnumber13 == 34
replace districtname13 = "Rotenburg I - Heidekreis" if districtnumber13 == 35
replace districtname13 = "Harburg" if districtnumber13 == 36
replace districtname13 = "Lüchow-Dannenberg - Lüneburg" if districtnumber13 == 37
replace districtname13 = "Osnabrück-Land" if districtnumber13 == 38
replace districtname13 = "Stadt Osnabrück" if districtnumber13 == 39
replace districtname13 = "Nienburg II - Schaumburg" if districtnumber13 == 40
replace districtname13 = "Stadt Hannover I" if districtnumber13 == 41
replace districtname13 = "Stadt Hannover II" if districtnumber13 == 42
replace districtname13 = "Hannover-Land I" if districtnumber13 == 43
replace districtname13 = "Celle - Uelzen" if districtnumber13 == 44
replace districtname13 = "Gifhorn - Peine" if districtnumber13 == 45
replace districtname13 = "Hameln-Pyrmont - Holzminden" if districtnumber13 == 46
replace districtname13 = "Hannover-Land II" if districtnumber13 == 47
replace districtname13 = "Hildesheim" if districtnumber13 == 48
replace districtname13 = "Salzgitter - Wolfenbüttel" if districtnumber13 == 49
replace districtname13 = "Braunschweig" if districtnumber13 == 50
replace districtname13 = "Helmstedt - Wolfsburg" if districtnumber13 == 51
replace districtname13 = "Goslar - Northeim - Osterode" if districtnumber13 == 52
replace districtname13 = "Göttingen" if districtnumber13 == 53
replace districtname13 = "Niedersachsen" if districtnumber13 == 903
replace districtname13 = "Bremen I" if districtnumber13 == 54
replace districtname13 = "Bremen II - Bremerhaven" if districtnumber13 == 55
replace districtname13 = "Bremen" if districtnumber13 == 904
replace districtname13 = "Aachen I" if districtnumber13 == 87
replace districtname13 = "Aachen II" if districtnumber13 == 88
replace districtname13 = "Heinsberg" if districtnumber13 == 89
replace districtname13 = "Düren" if districtnumber13 == 90
replace districtname13 = "Rhein-Erft-Kreis I" if districtnumber13 == 91
replace districtname13 = "Euskirchen - Rhein-Erft-Kreis II" if districtnumber13 == 92
replace districtname13 = "Köln I" if districtnumber13 == 93
replace districtname13 = "Köln II" if districtnumber13 == 94
replace districtname13 = "Köln III" if districtnumber13 == 95
replace districtname13 = "Bonn" if districtnumber13 == 96
replace districtname13 = "Rhein-Sieg-Kreis I" if districtnumber13 == 97
replace districtname13 = "Rhein-Sieg-Kreis II" if districtnumber13 == 98
replace districtname13 = "Oberbergischer Kreis" if districtnumber13 == 99
replace districtname13 = "Rheinisch-Bergischer Kreis" if districtnumber13 == 100
replace districtname13 = "Leverkusen - Köln IV" if districtnumber13 == 101
replace districtname13 = "Wuppertal I" if districtnumber13 == 102
replace districtname13 = "Solingen - Remscheid - Wuppertal II" if districtnumber13 == 103
replace districtname13 = "Mettmann I" if districtnumber13 == 104
replace districtname13 = "Mettmann II" if districtnumber13 == 105
replace districtname13 = "Düsseldorf I" if districtnumber13 == 106
replace districtname13 = "Düsseldorf II" if districtnumber13 == 107
replace districtname13 = "Neuss I" if districtnumber13 == 108
replace districtname13 = "Mönchengladbach" if districtnumber13 == 109
replace districtname13 = "Krefeld I - Neuss II" if districtnumber13 == 110
replace districtname13 = "Viersen" if districtnumber13 == 111
replace districtname13 = "Kleve" if districtnumber13 == 112
replace districtname13 = "Wesel I" if districtnumber13 == 113
replace districtname13 = "Krefeld II - Wesel II" if districtnumber13 == 114
replace districtname13 = "Duisburg I" if districtnumber13 == 115
replace districtname13 = "Duisburg II" if districtnumber13 == 116
replace districtname13 = "Oberhausen - Wesel III" if districtnumber13 == 117
replace districtname13 = "Mülheim - Essen I" if districtnumber13 == 118
replace districtname13 = "Essen II" if districtnumber13 == 119
replace districtname13 = "Essen III" if districtnumber13 == 120
replace districtname13 = "Recklinghausen I" if districtnumber13 == 121
replace districtname13 = "Recklinghausen II" if districtnumber13 == 122
replace districtname13 = "Gelsenkirchen" if districtnumber13 == 123
replace districtname13 = "Steinfurt I - Borken I" if districtnumber13 == 124
replace districtname13 = "Bottrop - Recklinghausen III" if districtnumber13 == 125
replace districtname13 = "Borken II" if districtnumber13 == 126
replace districtname13 = "Coesfeld - Steinfurt II" if districtnumber13 == 127
replace districtname13 = "Steinfurt III" if districtnumber13 == 128
replace districtname13 = "Münster" if districtnumber13 == 129
replace districtname13 = "Warendorf" if districtnumber13 == 130
replace districtname13 = "Gütersloh I" if districtnumber13 == 131
replace districtname13 = "Bielefeld - Gütersloh II" if districtnumber13 == 132
replace districtname13 = "Herford - Minden-Lübbecke II" if districtnumber13 == 133
replace districtname13 = "Minden-Lübbecke I" if districtnumber13 == 134
replace districtname13 = "Lippe I" if districtnumber13 == 135
replace districtname13 = "Höxter - Lippe II" if districtnumber13 == 136
replace districtname13 = "Paderborn - Gütersloh III" if districtnumber13 == 137
replace districtname13 = "Hagen - Ennepe-Ruhr-Kreis I" if districtnumber13 == 138
replace districtname13 = "Ennepe-Ruhr-Kreis II" if districtnumber13 == 139
replace districtname13 = "Bochum I" if districtnumber13 == 140
replace districtname13 = "Herne - Bochum II" if districtnumber13 == 141
replace districtname13 = "Dortmund I" if districtnumber13 == 142
replace districtname13 = "Dortmund II" if districtnumber13 == 143
replace districtname13 = "Unna I" if districtnumber13 == 144
replace districtname13 = "Hamm - Unna II" if districtnumber13 == 145
replace districtname13 = "Soest" if districtnumber13 == 146
replace districtname13 = "Hochsauerlandkreis" if districtnumber13 == 147
replace districtname13 = "Siegen-Wittgenstein" if districtnumber13 == 148
replace districtname13 = "Olpe - Märkischer Kreis I" if districtnumber13 == 149
replace districtname13 = "Märkischer Kreis II" if districtnumber13 == 150
replace districtname13 = "Nordrhein-Westfalen" if districtnumber13 == 905
replace districtname13 = "Waldeck" if districtnumber13 == 167
replace districtname13 = "Kassel" if districtnumber13 == 168
replace districtname13 = "Werra-Meißner - Hersfeld-Rotenburg" if districtnumber13 == 169
replace districtname13 = "Schwalm-Eder" if districtnumber13 == 170
replace districtname13 = "Marburg" if districtnumber13 == 171
replace districtname13 = "Lahn-Dill" if districtnumber13 == 172
replace districtname13 = "Gießen" if districtnumber13 == 173
replace districtname13 = "Fulda" if districtnumber13 == 174
replace districtname13 = "Main-Kinzig - Wetterau II - Schotten" if districtnumber13 == 175
replace districtname13 = "Hochtaunus" if districtnumber13 == 176
replace districtname13 = "Wetterau I" if districtnumber13 == 177
replace districtname13 = "Rheingau-Taunus - Limburg" if districtnumber13 == 178
replace districtname13 = "Wiesbaden" if districtnumber13 == 179
replace districtname13 = "Hanau" if districtnumber13 == 180
replace districtname13 = "Main-Taunus" if districtnumber13 == 181
replace districtname13 = "Frankfurt am Main I" if districtnumber13 == 182
replace districtname13 = "Frankfurt am Main II" if districtnumber13 == 183
replace districtname13 = "Groß-Gerau" if districtnumber13 == 184
replace districtname13 = "Offenbach" if districtnumber13 == 185
replace districtname13 = "Darmstadt" if districtnumber13 == 186
replace districtname13 = "Odenwald" if districtnumber13 == 187
replace districtname13 = "Bergstraße" if districtnumber13 == 188
replace districtname13 = "Hessen" if districtnumber13 == 906
replace districtname13 = "Neuwied" if districtnumber13 == 198
replace districtname13 = "Ahrweiler" if districtnumber13 == 199
replace districtname13 = "Koblenz" if districtnumber13 == 200
replace districtname13 = "Mosel/Rhein-Hunsrück" if districtnumber13 == 201
replace districtname13 = "Kreuznach" if districtnumber13 == 202
replace districtname13 = "Bitburg" if districtnumber13 == 203
replace districtname13 = "Trier" if districtnumber13 == 204
replace districtname13 = "Montabaur" if districtnumber13 == 205
replace districtname13 = "Mainz" if districtnumber13 == 206
replace districtname13 = "Worms" if districtnumber13 == 207
replace districtname13 = "Ludwigshafen/Frankenthal" if districtnumber13 == 208
replace districtname13 = "Neustadt - Speyer" if districtnumber13 == 209
replace districtname13 = "Kaiserslautern" if districtnumber13 == 210
replace districtname13 = "Pirmasens" if districtnumber13 == 211
replace districtname13 = "Südpfalz" if districtnumber13 == 212
replace districtname13 = "Rheinland-Pfalz" if districtnumber13 == 907
replace districtname13 = "Stuttgart I" if districtnumber13 == 258
replace districtname13 = "Stuttgart II" if districtnumber13 == 259
replace districtname13 = "Böblingen" if districtnumber13 == 260
replace districtname13 = "Esslingen" if districtnumber13 == 261
replace districtname13 = "Nürtingen" if districtnumber13 == 262
replace districtname13 = "Göppingen" if districtnumber13 == 263
replace districtname13 = "Waiblingen" if districtnumber13 == 264
replace districtname13 = "Ludwigsburg" if districtnumber13 == 265
replace districtname13 = "Neckar-Zaber" if districtnumber13 == 266
replace districtname13 = "Heilbronn" if districtnumber13 == 267
replace districtname13 = "Schwäbisch Hall - Hohenlohe" if districtnumber13 == 268
replace districtname13 = "Backnang - Schwäbisch Gmünd" if districtnumber13 == 269
replace districtname13 = "Aalen - Heidenheim" if districtnumber13 == 270
replace districtname13 = "Karlsruhe-Stadt" if districtnumber13 == 271
replace districtname13 = "Karlsruhe-Land" if districtnumber13 == 272
replace districtname13 = "Rastatt" if districtnumber13 == 273
replace districtname13 = "Heidelberg" if districtnumber13 == 274
replace districtname13 = "Mannheim" if districtnumber13 == 275
replace districtname13 = "Odenwald - Tauber" if districtnumber13 == 276
replace districtname13 = "Rhein-Neckar" if districtnumber13 == 277
replace districtname13 = "Bruchsal - Schwetzingen" if districtnumber13 == 278
replace districtname13 = "Pforzheim" if districtnumber13 == 279
replace districtname13 = "Calw" if districtnumber13 == 280
replace districtname13 = "Freiburg" if districtnumber13 == 281
replace districtname13 = "Lörrach - Müllheim" if districtnumber13 == 282
replace districtname13 = "Emmendingen - Lahr" if districtnumber13 == 283
replace districtname13 = "Offenburg" if districtnumber13 == 284
replace districtname13 = "Rottweil - Tuttlingen" if districtnumber13 == 285
replace districtname13 = "Schwarzwald-Baar" if districtnumber13 == 286
replace districtname13 = "Konstanz" if districtnumber13 == 287
replace districtname13 = "Waldshut" if districtnumber13 == 288
replace districtname13 = "Reutlingen" if districtnumber13 == 289
replace districtname13 = "Tübingen" if districtnumber13 == 290
replace districtname13 = "Ulm" if districtnumber13 == 291
replace districtname13 = "Biberach" if districtnumber13 == 292
replace districtname13 = "Bodensee" if districtnumber13 == 293
replace districtname13 = "Ravensburg" if districtnumber13 == 294
replace districtname13 = "Zollernalb - Sigmaringen" if districtnumber13 == 295
replace districtname13 = "Baden-Württemberg" if districtnumber13 == 908
replace districtname13 = "Altötting" if districtnumber13 == 213
replace districtname13 = "Erding - Ebersberg" if districtnumber13 == 214
replace districtname13 = "Freising" if districtnumber13 == 215
replace districtname13 = "Fürstenfeldbruck" if districtnumber13 == 216
replace districtname13 = "Ingolstadt" if districtnumber13 == 217
replace districtname13 = "München-Nord" if districtnumber13 == 218
replace districtname13 = "München-Ost" if districtnumber13 == 219
replace districtname13 = "München-Süd" if districtnumber13 == 220
replace districtname13 = "München-West/Mitte" if districtnumber13 == 221
replace districtname13 = "München-Land" if districtnumber13 == 222
replace districtname13 = "Rosenheim" if districtnumber13 == 223
replace districtname13 = "Starnberg" if districtnumber13 == 224
replace districtname13 = "Traunstein" if districtnumber13 == 225
replace districtname13 = "Weilheim" if districtnumber13 == 226
replace districtname13 = "Deggendorf" if districtnumber13 == 227
replace districtname13 = "Landshut" if districtnumber13 == 228
replace districtname13 = "Passau" if districtnumber13 == 229
replace districtname13 = "Rottal-Inn" if districtnumber13 == 230
replace districtname13 = "Straubing" if districtnumber13 == 231
replace districtname13 = "Amberg" if districtnumber13 == 232
replace districtname13 = "Regensburg" if districtnumber13 == 233
replace districtname13 = "Schwandorf" if districtnumber13 == 234
replace districtname13 = "Weiden" if districtnumber13 == 235
replace districtname13 = "Bamberg" if districtnumber13 == 236
replace districtname13 = "Bayreuth" if districtnumber13 == 237
replace districtname13 = "Coburg" if districtnumber13 == 238
replace districtname13 = "Hof" if districtnumber13 == 239
replace districtname13 = "Kulmbach" if districtnumber13 == 240
replace districtname13 = "Ansbach" if districtnumber13 == 241
replace districtname13 = "Erlangen" if districtnumber13 == 242
replace districtname13 = "Fürth" if districtnumber13 == 243
replace districtname13 = "Nürnberg-Nord" if districtnumber13 == 244
replace districtname13 = "Nürnberg-Süd" if districtnumber13 == 245
replace districtname13 = "Roth" if districtnumber13 == 246
replace districtname13 = "Aschaffenburg" if districtnumber13 == 247
replace districtname13 = "Bad Kissingen" if districtnumber13 == 248
replace districtname13 = "Main-Spessart" if districtnumber13 == 249
replace districtname13 = "Schweinfurt" if districtnumber13 == 250
replace districtname13 = "Würzburg" if districtnumber13 == 251
replace districtname13 = "Augsburg-Stadt" if districtnumber13 == 252
replace districtname13 = "Augsburg-Land" if districtnumber13 == 253
replace districtname13 = "Donau-Ries" if districtnumber13 == 254
replace districtname13 = "Neu-Ulm" if districtnumber13 == 255
replace districtname13 = "Oberallgäu" if districtnumber13 == 256
replace districtname13 = "Ostallgäu" if districtnumber13 == 257
replace districtname13 = "Bayern" if districtnumber13 == 909
replace districtname13 = "Saarbrücken" if districtnumber13 == 296
replace districtname13 = "Saarlouis" if districtnumber13 == 297
replace districtname13 = "St. Wendel" if districtnumber13 == 298
replace districtname13 = "Homburg" if districtnumber13 == 299
replace districtname13 = "Saarland" if districtnumber13 == 910
replace districtname13 = "Berlin-Mitte" if districtnumber13 == 75
replace districtname13 = "Berlin-Pankow" if districtnumber13 == 76
replace districtname13 = "Berlin-Reinickendorf" if districtnumber13 == 77
replace districtname13 = "Berlin-Spandau - Charlottenburg Nord" if districtnumber13 == 78
replace districtname13 = "Berlin-Steglitz - Zehlendorf" if districtnumber13 == 79
replace districtname13 = "Berlin-Charlottenburg - Wilmersdorf" if districtnumber13 == 80
replace districtname13 = "Berlin-Tempelhof - Schöneberg" if districtnumber13 == 81
replace districtname13 = "Berlin-Neukölln" if districtnumber13 == 82
replace districtname13 = "Berlin-Friedrichshain-Kreuzberg - Prenzlauer Berg Ost" if districtnumber13 == 83
replace districtname13 = "Berlin-Treptow - Köpenick" if districtnumber13 == 84
replace districtname13 = "Berlin-Marzahn - Hellersdorf" if districtnumber13 == 85
replace districtname13 = "Berlin-Lichtenberg" if districtnumber13 == 86
replace districtname13 = "Berlin" if districtnumber13 == 911
replace districtname13 = "Prignitz - Ostprignitz-Ruppin - Havelland I" if districtnumber13 == 56
replace districtname13 = "Uckermark - Barnim I" if districtnumber13 == 57
replace districtname13 = "Oberhavel - Havelland II" if districtnumber13 == 58
replace districtname13 = "Märkisch-Oderland - Barnim II" if districtnumber13 == 59
replace districtname13 = "Brandenburg an der Havel - Potsdam-Mittelmark I - Havelland III - Teltow-Fläming I" if districtnumber13 == 60
replace districtname13 = "Potsdam - Potsdam-Mittelmark II - Teltow-Fläming II" if districtnumber13 == 61
replace districtname13 = "Dahme-Spreewald - Teltow-Fläming III - Oberspreewald-Lausitz I" if districtnumber13 == 62
replace districtname13 = "Frankfurt (Oder) - Oder-Spree" if districtnumber13 == 63
replace districtname13 = "Cottbus - Spree-Neiße" if districtnumber13 == 64
replace districtname13 = "Elbe-Elster - Oberspreewald-Lausitz II" if districtnumber13 == 65
replace districtname13 = "Brandenburg" if districtnumber13 == 912
replace districtname13 = "Schwerin - Ludwigslust-Parchim I - Nordwestmecklenburg I" if districtnumber13 == 12
replace districtname13 = "Ludwigslust-Parchim II - Nordwestmecklenburg II - Landkreis Rostock I" if districtnumber13 == 13
replace districtname13 = "Rostock - Landkreis Rostock II" if districtnumber13 == 14
replace districtname13 = "Vorpommern-Rügen - Vorpommern-Greifswald I" if districtnumber13 == 15
replace districtname13 = "Mecklenburgische Seenplatte I - Vorpommern-Greifswald II" if districtnumber13 == 16
replace districtname13 = "Mecklenburgische Seenplatte II - Landkreis Rostock III" if districtnumber13 == 17
replace districtname13 = "Mecklenburg-Vorpommern" if districtnumber13 == 913
replace districtname13 = "Nordsachsen" if districtnumber13 == 151
replace districtname13 = "Leipzig I" if districtnumber13 == 152
replace districtname13 = "Leipzig II" if districtnumber13 == 153
replace districtname13 = "Leipzig-Land" if districtnumber13 == 154
replace districtname13 = "Meißen" if districtnumber13 == 155
replace districtname13 = "Bautzen I" if districtnumber13 == 156
replace districtname13 = "Görlitz" if districtnumber13 == 157
replace districtname13 = "Sächsische Schweiz - Osterzgebirge" if districtnumber13 == 158
replace districtname13 = "Dresden I" if districtnumber13 == 159
replace districtname13 = "Dresden II - Bautzen II" if districtnumber13 == 160
replace districtname13 = "Mittelsachsen" if districtnumber13 == 161
replace districtname13 = "Chemnitz" if districtnumber13 == 162
replace districtname13 = "Chemnitzer Umland - Erzgebirgskreis II" if districtnumber13 == 163
replace districtname13 = "Erzgebirgskreis I" if districtnumber13 == 164
replace districtname13 = "Zwickau" if districtnumber13 == 165
replace districtname13 = "Vogtlandkreis" if districtnumber13 == 166
replace districtname13 = "Sachsen" if districtnumber13 == 914
replace districtname13 = "Altmark" if districtnumber13 == 66
replace districtname13 = "Börde - Jerichower Land" if districtnumber13 == 67
replace districtname13 = "Harz" if districtnumber13 == 68
replace districtname13 = "Magdeburg" if districtnumber13 == 69
replace districtname13 = "Dessau - Wittenberg" if districtnumber13 == 70
replace districtname13 = "Anhalt" if districtnumber13 == 71
replace districtname13 = "Halle" if districtnumber13 == 72
replace districtname13 = "Burgenland - Saalekreis" if districtnumber13 == 73
replace districtname13 = "Mansfeld" if districtnumber13 == 74
replace districtname13 = "Sachsen-Anhalt" if districtnumber13 == 915
replace districtname13 = "Eichsfeld - Nordhausen - Unstrut-Hainich-Kreis I" if districtnumber13 == 189
replace districtname13 = "Eisenach - Wartburgkreis - Unstrut-Hainich-Kreis II" if districtnumber13 == 190
replace districtname13 = "Kyffhäuserkreis - Sömmerda - Weimarer Land I" if districtnumber13 == 191
replace districtname13 = "Gotha - Ilm-Kreis" if districtnumber13 == 192
replace districtname13 = "Erfurt - Weimar - Weimarer Land II" if districtnumber13 == 193
replace districtname13 = "Gera - Jena - Saale-Holzland-Kreis" if districtnumber13 == 194
replace districtname13 = "Greiz - Altenburger Land" if districtnumber13 == 195
replace districtname13 = "Sonneberg - Saalfeld-Rudolstadt - Saale-Orla-Kreis" if districtnumber13 == 196
replace districtname13 = "Suhl - Schmalkalden-Meiningen - Hildburghausen" if districtnumber13 == 197
replace districtname13 = "Thüringen" if districtnumber13 == 916
replace districtname13 = "Bundesgebiet" if districtnumber13 == 999

* Generate vote percentages

rename v09_1_ numberofvotes09

gen valid09 = strpos(var, "Gültige") > 0

gen validvotes09 = numberofvotes09 if valid09 == 1

gsort districtname13 validvotes09
bysort districtname13 : gen numberofvalidvotes09 = validvotes09[1]

drop if strpos(var, "Wahlberechtigte_1_Vor") > 0
drop if strpos(var, "Wähler_1_Vor") > 0
drop if strpos(var, "Ungültige_1_Vor") > 0
drop if strpos(var, "Gültige_1_Vor") > 0

gsort districtname13 -numberofvotes09

bysort districtname13 : gen rank09 = _n

gen numberofvotespercent09 = (numberofvotes09/numberofvalidvotes09)*100

drop if numberofvotespercent09 == .

* Generate rank of parties

gen first09 = party09 if rank09 == 1
gen second09 = party09 if rank09 == 2
gen third09 = party09 if rank09 == 3

bysort districtname13 : gen winner09 = first09[1]
bysort districtname13 : gen runnerup09 = second09[2]
bysort districtname13 : gen thirdplace09 = third09[3]

* Generate margins of victory

bysort districtname13 : gen margin09 = numberofvotespercent09[1] - numberofvotespercent09[2]

* Generate effective number of electoral parties

gen sqrvotepr09 = (numberofvotespercent09/100)^2

bysort districtname13 : egen sumsqrvotepr09 = sum(sqrvotepr09)

gen enep09 = 1/sumsqrvotepr09

* Labelling parties

label define party 1 "CDU/CSU" 4 "SPD" 5 "FDP" 6 "Green" 7 "Die Linke" 88 "Other"
label values winner09 runnerup09 thirdplace09 party

* Selecting, ordering, and sorting variables

keep districtnumber13 districtname13 party09 rank09 numberofvotes09 numberofvotespercent09 margin09 enep09 winner09 runnerup09 thirdplace09

order districtnumber13 districtname13 party09 rank09 numberofvotes09 numberofvotespercent09 margin09 enep09 winner09 runnerup09 thirdplace09

sort districtnumber13 rank09

drop if rank09 > 3

* Drop duplicates

duplicates tag districtnumber13, gen(dup)
duplicates drop districtnumber13, force
drop dup

* Saving file

save DE09_13_results, replace